package com.mtsmda.is.admin.domain.util.db;

import java.io.File;
import java.io.FileInputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class CreateConnection {

	public static final String MYSQL = "jdbc:mysql";
	public static final String SQLSERVER = "jdbc:sqlserver";
	public static final String LOCALHOST = "localhost";

	public static final String HOST = "host";
	public static final String ROOT = "root";
	public static final String PASSWORD = "password";
	public static final String NAMEDB = "nameDB";
	public static final String DBMS = "dbms";

	/*
	 * public static final Integer SYNCHRONIZATION = 1; public static final
	 * Integer NOT_SYNCHRONIZATION = 0;
	 */

	private static final String MYSQL_DRIVER = "com.mysql.jdbc.Driver";
	private static final String SQLSERVER_DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

	public static Integer countTablesInDatabase = 0;

	private String host;
	private String root;
	private String password;
	private String nameDB;
	private String url;
	private String dbms;

	private Properties properties = new Properties();
	private Connection connection;
	private ResultSet result = null;
	private Statement stmt;
	private PreparedStatement pstmt;

	private static CreateConnection instance;

	private CreateConnection(String host, String root, String password,
			String nameDB, String dbms) {
		this.host = host;
		this.root = root;
		this.password = password;
		this.nameDB = nameDB;
		this.dbms = dbms;
		initProperties();
	}

	private CreateConnection(Properties properties) {
		setDates(properties);
		initProperties();
	}

	private CreateConnection(File file) {
		Properties properties = getProperties(file);
		setDates(properties);
		initProperties();
	}

	private void setDates(Properties properties) {
		this.host = properties.getProperty("host");
		this.root = properties.getProperty("root");
		this.password = properties.getProperty("password");
		this.nameDB = properties.getProperty("nameDB");
		this.dbms = properties.getProperty("dbms");
	}

	private Properties getProperties(File file) {
		Properties properties = new Properties();
		try {
			if (file != null && file.isFile() && file.exists()) {

				FileInputStream fileInputStream = new FileInputStream(file);
				properties.load(fileInputStream);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return properties;
	}

	public static CreateConnection getInstance(String host, String root,
			String password, String nameDB, String dbms) {
		if (instance == null) {
			synchronized (CreateConnection.class) {
				if (instance == null) {
					instance = new CreateConnection(host, root, password,
							nameDB, dbms);
				}
			}
		}
		return instance;
	}

	public static CreateConnection getInstance(Properties properties) {
		if (instance == null) {
			synchronized (CreateConnection.class) {
				if (instance == null) {
					instance = new CreateConnection(properties);
				}
			}
		}
		return instance;
	}

	public static CreateConnection getInstance(File file) {
		if (instance == null) {
			synchronized (CreateConnection.class) {
				if (instance == null) {
					instance = new CreateConnection(file);
				}
			}
		}
		return instance;
	}

	/*
	 * public static CreateConnection getInstance(String host, String root,
	 * String password, String nameDB, String dbms, Integer synch) { if
	 * (instance == null) { if (synch == SYNCHRONIZATION) { synchronized
	 * (CreateConnection.class) { if (instance == null) { instance = new
	 * CreateConnection(host, root, password, nameDB, dbms); } } } else if
	 * (synch == NOT_SYNCHRONIZATION) { instance = new CreateConnection(host,
	 * root, password, nameDB, dbms); } else { instance = null; } } return
	 * instance; }
	 */

	public void initProperties() {
		// this.url = "jdbc:mysql://" + this.host + ":3306" + "/" + this.nameDB
		// + "?user=" + this.root
		// + "&password=" + this.password;
		if (this.host != "" && !this.host.isEmpty() && this.root != ""
				&& !this.root.isEmpty() && this.password != ""
				&& !this.password.isEmpty() && this.nameDB != ""
				&& !this.nameDB.isEmpty() && this.dbms != ""
				&& !this.dbms.isEmpty()) {
			if (dbms.equals(MYSQL)) {
				this.url = MYSQL + "://" + this.host + ":3306" + "/"
						+ this.nameDB + "?user=" + this.root + "&password="
						+ this.password;
			}// "jdbc:sqlserver://localhost:1433;databaseName=magazin";
			else if (dbms.equals(SQLSERVER)) {
				this.url = SQLSERVER + "://" + this.host + ":1433"
						+ ";databaseName=" + this.nameDB
						+ ";integratedSecurity=true";
			}
		} else {
			System.out
					.println("ERROR (host OR root OR password OR nameDB OR dbms)!!!");
			return;
		}
		properties.setProperty("user", this.root);
		properties.setProperty("password", this.password);
		properties.setProperty("characterEncoding", "UTF-8");
		properties.setProperty("useUnicode", "true");
		this.init();

		if (this.url == "" && this.url.isEmpty()) {
			System.out.println("ERROR URL " + this.url);
			return;
		}

	}

	private void init() {
		if (this.connection == null) {
			try {
				if (dbms.equals(MYSQL)) {
					Class.forName(MYSQL_DRIVER);
					this.connection = DriverManager.getConnection(url);
				} else if (dbms.equals(SQLSERVER)) {
					Class.forName(SQLSERVER_DRIVER).newInstance();
					this.connection = DriverManager.getConnection(url,
							this.root, this.password);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public Connection getConnection() {
		try {
			if (this.connection == null || this.connection.isClosed()) {
				if (dbms.equals(MYSQL)) {
					Class.forName(MYSQL_DRIVER);
					this.connection = DriverManager.getConnection(url);
				} else if (dbms.equals(SQLSERVER)) {
					Class.forName(SQLSERVER_DRIVER).newInstance();
					this.connection = DriverManager.getConnection(url,
							this.root, this.password);
				}

			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return this.connection;
	}

	public void finalize() {
		try {
			if (connection != null) {
				connection.close();
			}
			if (result != null) {
				result.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public ResultSet query(String query, boolean resultSetSettings) {
		try {
			if (resultSetSettings) {
				stmt = this.connection.createStatement(
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
			} else {
				stmt = this.connection.createStatement();
			}
			result = stmt.executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	private void definitionType(Object ob[], List<Object> parametrsList)
			throws SQLException {
		if (ob != null && ob.length > 0) {
			for (int i = 0; i < ob.length; i++) {
				if (ob[i] instanceof Integer) {
					pstmt.setInt((i + 1), (Integer) ob[i]);
				}
				if (ob[i] instanceof Long) {
					pstmt.setLong((i + 1), (Long) ob[i]);
				}
				if (ob[i] instanceof Byte) {
					pstmt.setByte((i + 1), (Byte) ob[i]);
				}
				if (ob[i] instanceof Short) {
					pstmt.setShort((i + 1), (Short) ob[i]);
				}
				if (ob[i] instanceof String || ob[i] instanceof Date) {
					pstmt.setString((i + 1), (String) ob[i]);
				}
				if (ob[i] instanceof Double || ob[i] instanceof Float) {
					pstmt.setDouble((i + 1), (Double) ob[i]);
				}
				if (ob[i] instanceof Boolean) {
					pstmt.setBoolean((i + 1), (Boolean) ob[i]);
				}
			}
		} else if (parametrsList != null && parametrsList.size() > 0) {
			for (int i = 0; i < parametrsList.size(); i++) {
				if (parametrsList.get(i) instanceof Integer) {
					pstmt.setInt((i + 1), (Integer) parametrsList.get(i));
				}
				if (parametrsList.get(i) instanceof Long) {
					pstmt.setLong((i + 1), (Long) parametrsList.get(i));
				}
				if (parametrsList.get(i) instanceof Byte) {
					pstmt.setByte((i + 1), (Byte) parametrsList.get(i));
				}
				if (parametrsList.get(i) instanceof Short) {
					pstmt.setShort((i + 1), (Short) parametrsList.get(i));
				}
				if (parametrsList.get(i) instanceof String
						|| parametrsList.get(i) instanceof Date) {
					pstmt.setString((i + 1), (String) parametrsList.get(i));
				}
				if (parametrsList.get(i) instanceof Double
						|| parametrsList.get(i) instanceof Float) {
					pstmt.setDouble((i + 1), (Double) parametrsList.get(i));
				}
				if (parametrsList.get(i) instanceof Boolean) {
					pstmt.setBoolean((i + 1), (Boolean) parametrsList.get(i));
				}
			}
		}

	}

	public ResultSet queryPrepared(String query, Object ob[],
			boolean resultSetSettings) {
		if (ob.length == 0) {
			return null;
		}
		try {
			if (resultSetSettings) {
				pstmt = this.connection.prepareStatement(query,
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
			} else {
				pstmt = this.connection.prepareStatement(query);
			}

			definitionType(ob, null);

			result = pstmt.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public ResultSet queryPrepared(String query, List<Object> parametrsList,
			boolean resultSetSettings) {
		if (parametrsList.size() == 0) {
			return null;
		}
		try {
			if (resultSetSettings) {
				pstmt = this.connection.prepareStatement(query,
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
			} else {
				pstmt = this.connection.prepareStatement(query);
			}

			definitionType(null, parametrsList);

			result = pstmt.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public int updateQueryPrepared(String query, Object[] ob) {
		int count = 0;
		try {
			pstmt = this.connection.prepareStatement(query);
			if (ob.length == 0) {
				return 0;
			}

			definitionType(ob, null);

			count = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	public int updateQueryPrepared(String query, List<Object> parametrsList) {
		int count = 0;
		try {
			pstmt = this.connection.prepareStatement(query);
			if (parametrsList.size() == 0) {
				return 0;
			}

			definitionType(null, parametrsList);

			count = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	public int updateQuery(String query) {
		int count = 0;
		try {
			stmt = this.connection.createStatement();
			count = stmt.executeUpdate(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	public int[] updateQueryBatch(String[] query) {
		if (query.length == 0) {
			return null;
		}
		int count[] = new int[query.length];
		try {
			stmt = this.connection.createStatement();
			this.connection.setAutoCommit(false);
			for (int i = 0; i < query.length; i++) {
				stmt.addBatch(query[i]);
			}
			count = stmt.executeBatch();
			this.connection.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				this.connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
		return count;
	}

	public ResultSetMetaData metaDates(String query, boolean resultSetSettings) {
		ResultSet query2 = query(query, resultSetSettings);
		ResultSetMetaData rsMetaData = null;
		try {
			rsMetaData = query2.getMetaData();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		return rsMetaData;
	}

	public DatabaseMetaData getDatabase() {
		DatabaseMetaData metaData = null;
		try {
			metaData = this.getConnection().getMetaData();

		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		return metaData;
	}

	public List<String> getTablesDatabase() {
		List<String> tablesAr = new ArrayList<String>();
		try {
			DatabaseMetaData metaData = getConnection().getMetaData();
			ResultSet tables = metaData.getTables(null, null, "%", null);
			while (tables.next()) {
				tablesAr.add(tables.getString(3));
			}
			countTablesInDatabase = tablesAr.size();
		} catch (SQLException e1) {
			e1.printStackTrace();
			return null;
		}
		return tablesAr;
	}

}